﻿/*
// --------------------------
// Copyright 2008 Daniel Gray
// --------------------------
// This file is part of Kale.
//
// Kale is free software: you can redistribute it and/or modify
// it under the terms of the GNU General Public License as published by
// the Free Software Foundation, either version 3 of the License, or
// (at your option) any later version.
//
// Kale is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
// GNU General Public License for more details.
// 
// You should have received a copy of the GNU General Public License
// along with Kale.  If not, see <http://www.gnu.org/licenses/>.
// --------------------------------------------------------
// Filename          : Schema.sql
// Author            : Daniel Gray
// Created           : 29/04/2008
// --------------------------------------------------------
*/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[User]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[User](
	[Uid] [uniqueidentifier] NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
	[Password] [binary](512) NOT NULL,
	[LastLogin] [datetime] NULL,
 CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED 
(
	[Uid] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UserGroup]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[UserGroup](
	[Uid] [uniqueidentifier] NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_UserGroup] PRIMARY KEY CLUSTERED 
(
	[Uid] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UserRole]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[UserRole](
	[Uid] [uniqueidentifier] NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_UserRole] PRIMARY KEY CLUSTERED 
(
	[Uid] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SessionObject]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[SessionObject](
	[Uid] [uniqueidentifier] NOT NULL,
	[SessionUid] [uniqueidentifier] NOT NULL,
	[ObjectKey] [nvarchar](max) NOT NULL,
	[ObjectBinary] [image] NOT NULL,
 CONSTRAINT [PK_SessionObject] PRIMARY KEY CLUSTERED 
(
	[Uid] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Session]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Session](
	[Uid] [uniqueidentifier] NOT NULL,
	[Login] [datetime] NOT NULL,
	[LastAccess] [datetime] NOT NULL,
	[Expiry] [datetime] NOT NULL,
	[UserUid] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_Session] PRIMARY KEY CLUSTERED 
(
	[Uid] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[User_UserRole]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[User_UserRole](
	[Uid] [uniqueidentifier] NOT NULL,
	[UserUid] [uniqueidentifier] NOT NULL,
	[UserRoleUid] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_User_UserRole] PRIMARY KEY CLUSTERED 
(
	[Uid] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[User_UserGroup]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[User_UserGroup](
	[Uid] [uniqueidentifier] NOT NULL,
	[UserUid] [uniqueidentifier] NOT NULL,
	[UserGroupUid] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_User_UserGroup] PRIMARY KEY CLUSTERED 
(
	[Uid] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UserGroup_UserGroup]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[UserGroup_UserGroup](
	[Uid] [uniqueidentifier] NOT NULL,
	[ParentUserGroupUid] [uniqueidentifier] NOT NULL,
	[ChildUserGroupUid] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_UserGroup_UserGroup] PRIMARY KEY CLUSTERED 
(
	[Uid] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UserGroup_UserRole]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[UserGroup_UserRole](
	[Uid] [uniqueidentifier] NOT NULL,
	[UserGroupUid] [uniqueidentifier] NOT NULL,
	[UserRoleUid] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_UserGroup_UserRole] PRIMARY KEY CLUSTERED 
(
	[Uid] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_SessionObject_Session]') AND parent_object_id = OBJECT_ID(N'[dbo].[SessionObject]'))
ALTER TABLE [dbo].[SessionObject]  WITH CHECK ADD  CONSTRAINT [FK_SessionObject_Session] FOREIGN KEY([SessionUid])
REFERENCES [dbo].[Session] ([Uid])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[SessionObject] CHECK CONSTRAINT [FK_SessionObject_Session]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Session_User]') AND parent_object_id = OBJECT_ID(N'[dbo].[Session]'))
ALTER TABLE [dbo].[Session]  WITH CHECK ADD  CONSTRAINT [FK_Session_User] FOREIGN KEY([UserUid])
REFERENCES [dbo].[User] ([Uid])
GO
ALTER TABLE [dbo].[Session] CHECK CONSTRAINT [FK_Session_User]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_User_UserRole_User]') AND parent_object_id = OBJECT_ID(N'[dbo].[User_UserRole]'))
ALTER TABLE [dbo].[User_UserRole]  WITH CHECK ADD  CONSTRAINT [FK_User_UserRole_User] FOREIGN KEY([UserUid])
REFERENCES [dbo].[User] ([Uid])
GO
ALTER TABLE [dbo].[User_UserRole] CHECK CONSTRAINT [FK_User_UserRole_User]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_User_UserRole_UserRole]') AND parent_object_id = OBJECT_ID(N'[dbo].[User_UserRole]'))
ALTER TABLE [dbo].[User_UserRole]  WITH CHECK ADD  CONSTRAINT [FK_User_UserRole_UserRole] FOREIGN KEY([UserRoleUid])
REFERENCES [dbo].[UserRole] ([Uid])
GO
ALTER TABLE [dbo].[User_UserRole] CHECK CONSTRAINT [FK_User_UserRole_UserRole]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_User_UserGroup_User]') AND parent_object_id = OBJECT_ID(N'[dbo].[User_UserGroup]'))
ALTER TABLE [dbo].[User_UserGroup]  WITH CHECK ADD  CONSTRAINT [FK_User_UserGroup_User] FOREIGN KEY([UserUid])
REFERENCES [dbo].[User] ([Uid])
GO
ALTER TABLE [dbo].[User_UserGroup] CHECK CONSTRAINT [FK_User_UserGroup_User]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_User_UserGroup_UserGroup]') AND parent_object_id = OBJECT_ID(N'[dbo].[User_UserGroup]'))
ALTER TABLE [dbo].[User_UserGroup]  WITH CHECK ADD  CONSTRAINT [FK_User_UserGroup_UserGroup] FOREIGN KEY([UserGroupUid])
REFERENCES [dbo].[UserGroup] ([Uid])
GO
ALTER TABLE [dbo].[User_UserGroup] CHECK CONSTRAINT [FK_User_UserGroup_UserGroup]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_UserGroup_UserGroup_UserGroup]') AND parent_object_id = OBJECT_ID(N'[dbo].[UserGroup_UserGroup]'))
ALTER TABLE [dbo].[UserGroup_UserGroup]  WITH CHECK ADD  CONSTRAINT [FK_UserGroup_UserGroup_UserGroup] FOREIGN KEY([ParentUserGroupUid])
REFERENCES [dbo].[UserGroup] ([Uid])
GO
ALTER TABLE [dbo].[UserGroup_UserGroup] CHECK CONSTRAINT [FK_UserGroup_UserGroup_UserGroup]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_UserGroup_UserGroup_UserGroup1]') AND parent_object_id = OBJECT_ID(N'[dbo].[UserGroup_UserGroup]'))
ALTER TABLE [dbo].[UserGroup_UserGroup]  WITH CHECK ADD  CONSTRAINT [FK_UserGroup_UserGroup_UserGroup1] FOREIGN KEY([ChildUserGroupUid])
REFERENCES [dbo].[UserGroup] ([Uid])
GO
ALTER TABLE [dbo].[UserGroup_UserGroup] CHECK CONSTRAINT [FK_UserGroup_UserGroup_UserGroup1]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_UserGroup_UserRole_UserGroup]') AND parent_object_id = OBJECT_ID(N'[dbo].[UserGroup_UserRole]'))
ALTER TABLE [dbo].[UserGroup_UserRole]  WITH CHECK ADD  CONSTRAINT [FK_UserGroup_UserRole_UserGroup] FOREIGN KEY([UserGroupUid])
REFERENCES [dbo].[UserGroup] ([Uid])
GO
ALTER TABLE [dbo].[UserGroup_UserRole] CHECK CONSTRAINT [FK_UserGroup_UserRole_UserGroup]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_UserGroup_UserRole_UserRole]') AND parent_object_id = OBJECT_ID(N'[dbo].[UserGroup_UserRole]'))
ALTER TABLE [dbo].[UserGroup_UserRole]  WITH CHECK ADD  CONSTRAINT [FK_UserGroup_UserRole_UserRole] FOREIGN KEY([UserRoleUid])
REFERENCES [dbo].[UserRole] ([Uid])
GO
ALTER TABLE [dbo].[UserGroup_UserRole] CHECK CONSTRAINT [FK_UserGroup_UserRole_UserRole]
